﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;

namespace ICarPool.BLL
{
    [Serializable]
    public class QueryBuilder
    {
        /// <summary>
        /// Contains filter information
        /// </summary>
        protected ArrayList filters = null;

        /// <summary>
        /// Contains sort information
        /// </summary>
        protected SortedList orders = null;

        /// <summary>
        /// The page number you are going to move. based on 0.
        /// </summary>
        protected int pageNumToGo = 0;

        /// <summary>
        /// The number of rows to display in one page. 0 means no paging.
        /// </summary>
        protected int rowsPerPage = 0;

        /// <summary>
        /// Empty constructor
        /// </summary>
        public QueryBuilder()
        {
            filters = new ArrayList();
            orders = new SortedList();
        }

        /// <summary>
        /// The page number you are going to move. based on 0.
        /// </summary>
        public int PageNumToGo
        {
            get { return pageNumToGo; }
            set { pageNumToGo = value; }
        }

        /// <summary>
        /// The number of rows to display in one page
        /// </summary>
        public int RowsPerPage
        {
            get { return rowsPerPage; }
            set { rowsPerPage = value; }
        }

        /// <summary>
        /// The last row index to display in this page. Start from 1
        /// </summary>
        public int LastRowNumToGet
        {
            get { return (pageNumToGo + 1) * rowsPerPage; }
        }

        /// <summary>
        /// The first row index to display in this page.Start from 1
        /// </summary>
        public int FirstRowNumToGet
        {
            get { return pageNumToGo * rowsPerPage + 1; }
        }

        /// <summary>
        ///  Add new filter
        /// </summary>
        /// <param name="filterName">The single or combined fileds to filter</param>
        /// <param name="filterOperator">The operation on the filter</param>
        /// <param name="filterValue">The value to compare</param>
        public void AddFilter(string filterName, string filterOperator, Int64 filterValue)
        {
            filters.Add(new SqlFilter(filterName, filterOperator, filterValue, FilterType.Numeric));
        }

        /// <summary>
        ///  Add new filter
        /// </summary>
        /// <param name="filterName">The single or combined fileds to filter</param>
        /// <param name="filterOperator">The operation on the filter</param>
        /// <param name="filterValue">The value to compare</param>
        public void AddFilter(string filterName, string filterOperator, string filterValue)
        {
            filters.Add(new SqlFilter(filterName, filterOperator, filterValue, FilterType.String));
        }

        /// <summary>
        ///  Add new filter
        /// </summary>
        /// <param name="filterName">The single or combined fileds to filter</param>
        /// <param name="filterOperator">The operation on the filter</param>
        /// <param name="filterValue">The value to compare</param>
        public void AddFilter(string filterName, string filterOperator, DateTime filterValue)
        {
            filters.Add(new SqlFilter(filterName, filterOperator, filterValue, FilterType.DateTime));
        }

        /// <summary>
        ///  Add new filter
        /// </summary>
        /// <param name="filterName">The single or combined fileds to filter</param>
        /// <param name="filterOperator">The operation on the filter</param>
        /// <param name="filterValue">The value to compare</param>
        public void AddFilter(string filterName, string filterOperator, decimal filterValue)
        {
            filters.Add(new SqlFilter(filterName, filterOperator, filterValue, FilterType.Numeric));
        }

        /// <summary>
        ///  Add new filter
        /// </summary>
        /// <param name="filterName">The single or combined fileds to filter</param>
        /// <param name="filterOperator">The operation on the filter</param>
        /// <param name="filterValue">The value to compare</param>
        public void AddFilter(string filterName, string filterOperator, double filterValue)
        {
            filters.Add(new SqlFilter(filterName, filterOperator, filterValue, FilterType.Numeric));
        }

        /// <summary>
        ///  Add new filter
        /// </summary>
        /// <param name="filterName">The single or combined fileds to filter</param>
        /// <param name="filterOperator">The operation on the filter</param>
        /// <param name="filterValue">The value to compare</param>
        public void AddFilter(string filterName, string filterOperator, int filterValue)
        {
            filters.Add(new SqlFilter(filterName, filterOperator, filterValue, FilterType.Numeric));
        }

        /// <summary>
        ///  Add new filter
        /// </summary>
        /// <param name="filterName">The single or combined fileds to filter</param>
        /// <param name="filterOperator">The operation on the filter</param>
        /// <param name="filterValue">The value to compare</param>
        public void AddFilter(string filterName, string filterOperator, Single filterValue)
        {
            filters.Add(new SqlFilter(filterName, filterOperator, filterValue, FilterType.Numeric));
        }

        /// <summary>
        ///  Add new filter
        /// </summary>
        /// <param name="filterName">The single or combined fileds to filter</param>
        /// <param name="filterOperator">The operation on the filter</param>
        /// <param name="filterValue">The value to compare</param>
        public void AddFilter(string filterName, string filterOperator, Int16 filterValue)
        {
            filters.Add(new SqlFilter(filterName, filterOperator, filterValue, FilterType.Numeric));
        }

        /// <summary>
        ///  Add new filter
        /// </summary>
        /// <param name="filterName">The single or combined fileds to filter</param>
        /// <param name="filterOperator">The operation on the filter</param>
        /// <param name="filterValue">The value to compare</param>
        public void AddFilter(string filterName, string filterOperator, byte filterValue)
        {
            filters.Add(new SqlFilter(filterName, filterOperator, filterValue, FilterType.Numeric));
        }

        /// <summary>
        ///  Add new filter
        /// </summary>
        /// <param name="filterName">The single or combined fileds to filter</param>
        /// <param name="filterOperator">The operation on the filter</param>
        /// <param name="filterValue">The value to compare</param>
        /// <remarks>
        /// The value will be converted to 1 or 0 when compared in SQL
        /// </remarks>
        public void AddFilter(string filterName, string filterOperator, bool filterValue)
        {
            filters.Add(new SqlFilter(filterName, filterOperator, filterValue, FilterType.Boolean));
        }

        /// <summary>
        ///  Add new filter
        /// </summary>
        /// <param name="filterName">The single or combined fileds to filter</param>
        /// <param name="filterOperator">The operation on the filter</param>
        /// <param name="filterValue">The value to compare</param>
        /// <remarks>
        /// If you use this method, you have to cast the filter value to object first.
        /// If your filter value if not a single value, for example function, you can use this one.
        /// </remarks>
        /// <example>
        ///		<code>
        ///			ISQLBuilder sqlBuilder = ConfigurationSetting.GetSQLBuilder();
        ///			sqlBuilder.AddFilter("ShipDate", "=", (object)"getDate()");
        ///		</code>
        /// </example>
        public void AddFilter(string filterName, string filterOperator, object filterValue)
        {
            filters.Add(new SqlFilter(filterName, filterOperator, filterValue, FilterType.Unknown));
        }

        /// <summary>
        ///  Remove a filter by filed name
        /// </summary>
        /// <param name="filterName">the name of the filed which will be removed</param>
        /// <returns>the count of filters removed</returns>
        public int RemoveFilter(string filterName)
        {
            if (filters.Count == 0)
                return 0;

            int removed = 0;
            for (int filterIndex = filters.Count - 1; filterIndex >= 0; filterIndex--)
            {
                SqlFilter filter = (SqlFilter)filters[filterIndex];
                if (filter.Name == filterName.ToUpper())
                {
                    filters.RemoveAt(filterIndex);
                    removed++;
                }
            }

            return removed;
        }

        /// <summary>
        /// Remove all the filters
        /// </summary>
        public void ClearFilter()
        {
            filters.RemoveRange(0, filters.Count);
        }

        /// <summary>
        /// The count of filters existing
        /// </summary>
        public int FilterCount
        {
            get
            {
                return filters.Count;
            }
        }

        /// <summary>
        /// Check whether a specific filter existing by field name
        /// </summary>
        /// <param name="filterName">the name of field in the filter</param>
        /// <returns>true if existing, otherwsie false</returns>
        public bool IsFilterExisting(string filterName)
        {
            for (int filterIndex = 0; filterIndex < filters.Count; filterIndex++)
            {
                SqlFilter filter = (SqlFilter)filters[filterIndex];
                if (filter.Name == filterName.ToUpper())
                {
                    return true;
                }
            }
            return false;
        }

        /// <summary>
        /// Get the filter array of specified field name
        /// </summary>
        /// <param name="filterName">the name of the field in the filter</param>
        /// <returns>A array of the SqlFilter</returns>
        /// <remarks>The compare of the name is case insensitive</remarks>
        public SqlFilter[] GetFilter(string filterName)
        {
            if (filters.Count == 0)
                return null;

            ArrayList filterList = new ArrayList();
            SqlFilter filter;
            for (int filterIndex = 0; filterIndex < filters.Count; filterIndex++)
            {
                filter = (SqlFilter)filters[filterIndex];
                if (filter.Name == filterName.ToUpper())
                {
                    filterList.Add(filter);
                }
            }

            if (filterList.Count == 0)
            {
                return null;
            }
            else
            {
                SqlFilter[] returnArray = new SqlFilter[filterList.Count];
                for (int filterIndex = 0; filterIndex < filterList.Count; filterIndex++)
                    returnArray[filterIndex] = (SqlFilter)filterList[filterIndex];
                return returnArray;
            }
        }

        /// <summary>
        /// Add a order by ASC
        /// </summary>
        /// <param name="orderName">The field name which will be sorted</param>		
        public void AddOrderASC(string orderName)
        {
            if (orders.ContainsKey(orderName.ToUpper()))
            {
                // overwrite the existing order
                orders.Remove(orderName.ToUpper());
            }
            orders.Add(orderName.ToUpper(), new SqlOrder(orderName, "ASC"));
        }


        /// <summary>
        /// Add a order by DESC
        /// </summary>
        /// <param name="orderName">The field name which will be sorted</param>		
        public void AddOrderDESC(string orderName)
        {
            if (orders.ContainsKey(orderName.ToUpper()))
            {
                // overwrite the existing order
                orders.Remove(orderName.ToUpper());
            }
            orders.Add(orderName.ToUpper(), new SqlOrder(orderName, "DESC"));
        }

        /// <summary>
        /// Remove specified order 
        /// </summary>
        /// <param name="orderName">The name of the order will be removed</param>
        /// <returns>0 if no such order otherwise 1</returns>
        public int RemoveOrder(string orderName)
        {
            if (orders.ContainsKey(orderName.ToUpper()))
            {
                orders.Remove(orderName.ToUpper());
                return 1;
            }
            else
            {
                return 0;
            }
        }

        /// <summary>
        /// Remove all the orders
        /// </summary>
        public void ClearOrder()
        {
            orders.Clear();
        }

        /// <summary>
        /// The count of orders existing
        /// </summary>
        public int OrderCount
        {
            get
            {
                return orders.Count;
            }
        }

        /// <summary>
        /// Check whether a specified order existing
        /// </summary>
        /// <param name="orderName">the name of the order</param>
        /// <returns>return true if existing otherwise false</returns>
        public bool IsOrderExisting(string orderName)
        {
            return orders.ContainsKey(orderName.ToUpper());
        }


        /// <summary>
        /// Get a specified order. If the specified order not existing, an exception will be thrown out.
        /// </summary>
        /// <param name="orderName">the order name</param>
        /// <returns>A SqlOrder</returns>
        public SqlOrder GetOrder(string orderName)
        {
            if (orders.ContainsKey(orderName.ToUpper()))
            {
                return (SqlOrder)orders[orderName.ToUpper()];
            }
            else
            {
                throw new ArgumentOutOfRangeException("No such ORDER existing.");
            }
        }

        /// <summary>
        /// Clear all the filters and orders
        /// </summary>
        public void Clear()
        {
            ClearFilter();
            ClearOrder();
        }

        /// <summary>
        /// Get the WHERE sub statement of SQL, Something like "Name Like'Luo%' AND Age > 20"
        /// </summary>
        /// <remarks>
        /// If there is no filter, empty string will be returned
        /// </remarks>
        public string FilterString
        {
            get
            {
                if (filters.Count == 0)
                    return string.Empty;

                StringBuilder filterString = new StringBuilder();

                for (int filterIndex = 0; filterIndex < filters.Count; filterIndex++)
                {
                    SqlFilter filter = (SqlFilter)filters[filterIndex];
                    if (filterIndex == 0)
                        filterString.Append(" WHERE ");
                    else
                        filterString.Append(" AND ");

                    string strValue;

                    switch (filter.Type)
                    {
                        case FilterType.String:
                            if (filter.Operator.ToLower().Equals("like"))
                                strValue = filter.Value.ToString().Replace("*", "%");
                            //strValue = filter.Value.ToString() + "%";
                            else
                                strValue = filter.Value.ToString();

                            filterString.AppendFormat(" {0} {1} '{2}' ",
                                filter.Name, filter.Operator, strValue);

                            break;
                        case FilterType.DateTime:
                            strValue = ((DateTime)filter.Value).ToString("yyyy-MM-dd HH:mm:ss");

                            filterString.AppendFormat(" {0} {1} '{2}' ",
                                filter.Name, filter.Operator, strValue);

                            break;
                        case FilterType.Numeric:
                            strValue = filter.Value.ToString();

                            filterString.AppendFormat(" {0} {1} {2} ",
                                filter.Name, filter.Operator, strValue);

                            break;
                        case FilterType.Boolean:
                            strValue = (filter.Value.ToString().ToUpper() == "TRUE") ? "1" : "0";

                            filterString.AppendFormat(" {0} {1} {2} ",
                                filter.Name, filter.Operator, strValue);

                            break;
                        default:	// FilterType.Unknown 
                            strValue = filter.Value.ToString();

                            filterString.AppendFormat(" {0} {1} {2} ",
                                filter.Name, filter.Operator, strValue);

                            break;
                    }
                }
                return filterString.ToString();
            }
        }


        /// <summary>
        /// Get the Order By sub statement of SQL, something like "ORDER BY Name ASC, Age DESC"
        /// </summary>
        /// <remarks>
        /// If there is no sort, empty string will be returned
        /// </remarks>
        public string OrderString
        {
            get
            {
                if (orders.Count == 0)
                    return string.Empty;

                StringBuilder orderString = new StringBuilder();
                for (int orderIndex = 0; orderIndex < orders.Count; orderIndex++)
                {
                    SqlOrder order = (SqlOrder)orders.GetByIndex(orderIndex);

                    if (orderIndex == 0)
                        orderString.Append(" ORDER BY ");
                    else
                        orderString.Append(", ");

                    orderString.AppendFormat("{0} {1} ", order.Name, order.Direction);
                }

                return orderString.ToString();
            }
        }
    }
}
